from clickhouse_driver import Client
from datetime import datetime, timedelta

# ClickHouse连接配置
clickhouse_config = {
    'host': '10.205.50.181',
    'port': '9000',
    'user': 'ck',
    'password': 'PpnXhU5C57DK',
    'database': 'default',
    'settings': {
        'connect_timeout': 600,
        'receive_timeout': 30,
        'send_timeout': 30
    }
}

# 创建ClickHouse客户端
client = Client(**clickhouse_config)

# 获取最早日期
min_date = datetime.strptime('2025-08-22', "%Y-%m-%d")
#min_date = datetime.strptime('2023-11-09', "%Y-%m-%d")
add_data_date = datetime.strptime('2024-06-01', "%Y-%m-%d")
yesterday = datetime.now() - timedelta(days=1)

#只跑昨天的
current = yesterday
#用于补历史数据的 开始日期 结束日期由yesterday 值确定 使用days
#current = min_date
#yesterday = add_data_date
while current <= yesterday:
    pt = current.strftime("%Y-%m-%d")
    print(f"Processing {pt} ...")
    insert_sql = f"""

INSERT INTO `default`.ct259_dws_project_nginx_browsing_history_report_copy
(pt, `path`, repo_id, ip, utm_source, `ref`, uid, cid, id, url, uri_ab, uri_abc, path_with_namespace, project_id, project_name, from_id,index, show_path,gitcode_first_time,receive_time)

WITH
    log_path AS (
        SELECT
            pt,
            replaceRegexpOne(
                replaceRegexpOne(url, '^.*gitcode\.com', ''),
                '\\?.*',
                ''
            ) AS path_only,
            repo_id,
            ip,
            utm_source,
            ref,
            uid,
            cid,
            JSONExtractString(extend_json,'page-cookie','uuid_tt_dd') AS extend_cid,
            id,
            url,
            replaceRegexpAll(
                arrayStringConcat(arraySlice(splitByChar('/', ifNull(path_only, '')), 2, 2), '/') ,  '^/+|/+$', ''
            )AS uri_ab,
            replaceRegexpAll(
                arrayStringConcat(arraySlice(splitByChar('/', ifNull(path_only, '')), 2, 3), '/') ,'^/+|/+$',''
            )AS uri_abc,
            replaceRegexpAll(
                IF(
                    multiSearchFirstIndex(url, k.keywords) > 0,
                    substring(
                        url,
                        1,
                        position(url, k.keywords[multiSearchFirstIndex(url, k.keywords)]) - 1
                    ),
                    replaceRegexpOne(url, '\\?.*', '') 
                ),'^/+|/+$',''
            )AS show_path,
            gitcode_first_time,
            receive_time
        FROM ct259_ods_gitcode_event_log_pro
        CROSS JOIN (
            SELECT groupArray(keyword) AS keywords
            FROM (
                SELECT 'issues' AS keyword UNION ALL
                SELECT 'pull' UNION ALL
                SELECT 'discussion' UNION ALL
                SELECT 'analysis' UNION ALL
                SELECT 'tree' UNION ALL
                SELECT 'commit' UNION ALL
                SELECT 'pipeline' UNION ALL
                SELECT 'wiki' UNION ALL
                SELECT 'merge_requests' UNION ALL
                SELECT 'blob'
            )
        ) AS k
        WHERE toDate(pt) = toDate('{pt}')
          AND event_id = 'pageview'
    ),
    project_unique AS (
        SELECT
            path_with_namespace,
            id,
            name
        FROM ct259_dwd_projects_full_info_wide
    )

SELECT
    log.pt,
    log.path_only AS path,
    log.repo_id,
    log.ip,
    log.utm_source,
    log.ref,
    log.uid,
    COALESCE(log.cid, log.extend_cid) AS cid,
    log.id,
    log.url,
    log.uri_ab,
    log.uri_abc,
    project_unique.path_with_namespace,
    project_unique.id AS project_id,
    project_unique.name AS project_name,
    CASE 
        WHEN extract(log.ref, 'from_id=([^&]+)') IS NOT NULL 
             AND extract(log.ref, 'from_id=([^&]+)') != '' 
        THEN extract(log.ref, 'from_id=([^&]+)')
        WHEN extract(decodeURLComponent(log.url),'/([^/]+)\\.html$') IS NOT NULL 
             AND extract(decodeURLComponent(log.url),'/([^/]+)\\.html$') != ''
             AND match(log.url, '^https?://blog\.gitcode\.com/([^/]+)\.html$')  -- 只允许blog.gitcode.com
        THEN decrypt('aes-128-ecb',unhex(extract(decodeURLComponent(log.url),'/([^/]+)\\.html$')),'gj0D7jlJtN12Z5WU')
        ELSE ''
    END AS from_id,
    coalesce(extract(log.url, 'index=([^&]+)'), '') AS index,
    log.show_path,
    gitcode_first_time,
    receive_time
FROM log_path AS log
GLOBAL LEFT JOIN  ct259_dwd_ip_black_list  as black 
ON black.ip = log.ip
GLOBAL LEFT JOIN project_unique
    ON lower(log.uri_ab) = lower(project_unique.path_with_namespace)
    OR lower(log.uri_abc) = lower(project_unique.path_with_namespace)
WHERE (black.ip ='' or black.ip is NULL) and match(log.url, '^https?://blog\.gitcode\.com/([^/]+)\.html$')=1
    """
    print(f"SQL for {pt}:\n" + insert_sql )
    try:
        client.execute(insert_sql)
        print(f"Inserted data for {pt}")
    except Exception as e:
        print( + f"Error on {pt}: {e}")
    current += timedelta(days=1)